package com.css.zfzx.sjcj.modules.statistics.yhDisaster.repository.impl;

import com.css.zfzx.sjcj.common.vo.DivisionVo;
import com.css.zfzx.sjcj.modules.pojo.QueryVo;
import com.css.zfzx.sjcj.modules.statistics.fxEconomicLossLevelMap.repository.entity.FxEconomicLossLevelMapEntity;
import com.css.zfzx.sjcj.modules.statistics.yhDisaster.repository.YhDisasterRepository;
import com.css.zfzx.sjcj.modules.statistics.yhDisaster.repository.entity.YhDisasterEntity;
import com.css.zfzx.sjcj.modules.statistics.yhDisaster.repository.entity.YhDisasterVO;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.math.BigInteger;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Description: pc-statistics
 * @Author: lhl
 * @CreateDate: 2021/5/13 11:17
 */
@Repository
public class YhDisasterRepositoryImpl implements YhDisasterRepository {
    @PersistenceContext
    private EntityManager em;

    private static final Logger log = LoggerFactory.getLogger(YhDisasterRepositoryImpl.class);

    @Override
    public List<YhDisasterEntity> findCreateTime(LocalDateTime createTime, int identify) {
        StringBuilder sql = new StringBuilder("select * from yh_disaster");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if (createTime != null) {
            whereSql.append(" and create_time > :createTime");
        } else {
            whereSql.append(" and create_time is not null");
        }
        if (identify == 1) {
            whereSql.append(" and yh_project_type in ('住宅','大中小学校舍','医疗卫生设施', '社会服务保障设施', '商业中心')");
        } else if (identify == 2) {
            whereSql.append(" and yh_project_type like '%桥梁%'");
        } else if (identify == 3) {
            whereSql.append(" and yh_project_type like '%水%'");
        }
        // 删除标识为1：有效
        whereSql.append(" and is_valid = 1");
        whereSql.append(" and update_time is null");
        whereSql.append(" order by create_time desc");
        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), YhDisasterEntity.class);

        // 时间
        if (createTime != null) {
            //query.setParameter("createTime", createTime.toLocalDate()+" "+createTime.toLocalTime());
            query.setParameter("createTime", createTime);
        }
        List<YhDisasterEntity> list = query.getResultList();
        return list;
    }

    @Override
    public List<YhDisasterEntity> findUpdateTime(LocalDateTime updateTime, int identify) {
        StringBuilder sql = new StringBuilder("select * from yh_disaster");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if (updateTime != null) {
            whereSql.append(" and update_time > :updateTime");
        } else {
            whereSql.append(" and update_time is not null");
        }
        if (identify == 1) {
            whereSql.append(" and yh_project_type in ('住宅','大中小学校舍','医疗卫生设施', '社会服务保障设施', '商业中心')");
        } else if (identify == 2) {
            whereSql.append(" and yh_project_type like '%桥梁%'");
        } else if (identify == 3) {
            whereSql.append(" and yh_project_type like '%水%'");
        }
        whereSql.append(" order by update_time desc");
        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), YhDisasterEntity.class);
        // 时间
        if (updateTime != null) {
            query.setParameter("updateTime", updateTime);
        }
        List<YhDisasterEntity> list = query.getResultList();
        return list;
    }

    @Override
    public List<DivisionVo> count(int identify1, int identify) {
        StringBuilder sql = new StringBuilder("select province,city,area from yh_disaster");
        StringBuilder whereSql = new StringBuilder(" where is_valid = 1 ");
        if (identify1==1){
            whereSql.append(" and is_report_pg = '1'");
        }else if (identify1==2){
            whereSql.append(" and (is_report_pg = '0' or is_report_pg is null)");
        }
        if (identify == 1) {
            whereSql.append(" and yh_project_type in ('住宅','大中小学校舍','医疗卫生设施', '社会服务保障设施', '商业中心')");
        } else if (identify == 2) {
            whereSql.append(" and yh_project_type like '%桥梁%'");
        } else if (identify == 3) {
            whereSql.append(" and yh_project_type like '%水%'");
        }
        whereSql.append(" group by province,city,area");
        Query query = this.em.createNativeQuery(sql.append(whereSql).toString());

        List<Object[]> resultList = query.getResultList();
        List<DivisionVo> voList=new ArrayList<>();
        for (Object[] objects:resultList){
            DivisionVo vo=new DivisionVo();
            vo.setProvinceName(String.valueOf(objects[0]));
            vo.setCityName(String.valueOf(objects[1]));
            vo.setAreaName(String.valueOf(objects[2]));
            voList.add(vo);
        }
        return voList;
    }

    @Override
    public Page<YhDisasterVO> find(QueryVo queryVo, String userId, int curPage, int pageSize, int identify,
                                   String sort, String order) {
        StringBuilder sql = new StringBuilder("select province,city,area,update_user,create_time,yh_code," +
                "yh_field_type,yh_earth_intensity,yh_now_earth_qh,cvalue,rvalue,vvalue,pheq,is_up_to_standard," +
                "quality_status,quality_comment,is_report_pg" +
                " from yh_disaster");
        StringBuilder whereSql = new StringBuilder(" where is_valid = 1 ");
        if (StringUtils.isNotEmpty(queryVo.getProvince())){
            whereSql.append(" and province =:province");
        }
        if (StringUtils.isNotEmpty(queryVo.getCity())){
            whereSql.append(" and city =:city");
        }
        if (StringUtils.isNotEmpty(queryVo.getArea())){
            whereSql.append(" and area =:area");
        }
        if (StringUtils.isNotEmpty(queryVo.getStartTime())){
            whereSql.append(" and create_time >=:startTime");
        }
        if (StringUtils.isNotEmpty(queryVo.getEndTime())){
            whereSql.append(" and create_time <=:endTime");
        }
        if (identify == 1) {
            whereSql.append(" and extends1 = 'A'");//城镇住宅
        } else if (identify == 2) {
            whereSql.append(" and extends1 = 'B'");//城镇非住宅
        } else if (identify == 3) {
            whereSql.append(" and extends1 = 'D'");//农村独立住宅
        } else if (identify == 4) {
            whereSql.append(" and extends1 = 'C'");//农村集合住宅
        }else if (identify == 5) {
            whereSql.append(" and extends1 = 'H'");//农村辅助住宅
        } else if (identify == 6) {
            whereSql.append(" and extends1 = 'E'");//农村非住宅
        } else if (identify == 7) {
            whereSql.append(" and extends1 = 'F'");//市政桥梁
        } else if (identify == 8) {
            whereSql.append(" and extends1 = 'G'");//城市自来水
        }
        if (!StringUtils.isEmpty(sort)) {
            whereSql.append(" order by " + sort + " " + order);
        }
        //whereSql.append(" limit " + (curPage-1)*pageSize + "," + pageSize);
        Query query = this.em.createNativeQuery(sql.append(whereSql).toString());
        StringBuilder countSql = new StringBuilder("select count(*) from yh_disaster ");
        Query countQuery = this.em.createNativeQuery(countSql.append(whereSql).toString());
        if (StringUtils.isNotEmpty(queryVo.getProvince())){
            query.setParameter("province",queryVo.getProvince());
            countQuery.setParameter("province",queryVo.getProvince());
        }
        if (StringUtils.isNotEmpty(queryVo.getCity())){
            query.setParameter("city",queryVo.getCity());
            countQuery.setParameter("city",queryVo.getCity());
        }
        if (StringUtils.isNotEmpty(queryVo.getArea())){
            query.setParameter("area",queryVo.getArea());
            countQuery.setParameter("area",queryVo.getArea());
        }
        if (StringUtils.isNotEmpty(queryVo.getStartTime())){
            query.setParameter("startTime",queryVo.getStartTime());
            countQuery.setParameter("startTime",queryVo.getStartTime());
        }
        if (StringUtils.isNotEmpty(queryVo.getEndTime())){
            query.setParameter("endTime",queryVo.getEndTime());
            countQuery.setParameter("endTime",queryVo.getEndTime());
        }
        Pageable pageable = PageRequest.of(curPage - 1, pageSize);
        query.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
        query.setMaxResults(pageable.getPageSize());
        List<Object[]> list = query.getResultList();
        BigInteger count = (BigInteger) countQuery.getSingleResult();
        Page<YhDisasterVO> pcPage = new PageImpl<>(getVOs(list), pageable, count.longValue());
        return pcPage;
    }
    private List<YhDisasterVO> getVOs(List<Object[]> list){
        List<YhDisasterVO> vos=new ArrayList<>();
        for (Object[] objects : list){
            YhDisasterVO vo=new YhDisasterVO();
            vo.setProvince(String.valueOf(objects[0]));
            vo.setCity(String.valueOf(objects[1]));
            vo.setArea(String.valueOf(objects[2]));
            vo.setCreateUserId(String.valueOf(objects[3]));
            vo.setCreateTime((Date) objects[4]);
            vo.setYhCode(String.valueOf(objects[5]));
            vo.setYhFieldType(String.valueOf(objects[6]));
            vo.setYhEarthIntensity(String.valueOf(objects[7]));
            vo.setYhNowEarthQh(String.valueOf(objects[8]));
            vo.setCvalue((Float) objects[9]);
            vo.setRvalue((Float) objects[10]);
            vo.setVvalue((Float) objects[11]);
            vo.setPheq((Float) objects[12]);
            vo.setIsUpToStandard((Boolean) objects[13]);
            vo.setQualityStatus(String.valueOf(objects[14]));
            vo.setQualityComment(String.valueOf(objects[15]));
            vo.setIsReportPg(String.valueOf(objects[16]));
            vos.add(vo);
        }
        return vos;
    }
    @Override
    public List<YhDisasterVO> findNoPage(String userId, int identify) {
        StringBuilder sql = new StringBuilder("select province,city,area,update_user,create_time,yh_code," +
                "yh_field_type,yh_earth_intensity,yh_now_earth_qh,cvalue,rvalue,vvalue,pheq,is_up_to_standard," +
                "quality_status,quality_comment,is_report_pg " +
                " from yh_disaster");
        StringBuilder whereSql = new StringBuilder(" where is_valid = 1 ");
        if (identify == 1) {
            whereSql.append(" and extends1 = 'A'");//城镇住宅
        } else if (identify == 2) {
            whereSql.append(" and extends1 = 'B'");//城镇非住宅
        } else if (identify == 3) {
            whereSql.append(" and extends1 = 'D'");//农村独立住宅
        } else if (identify == 4) {
            whereSql.append(" and extends1 = 'C'");//农村集合住宅
        } else if (identify == 5) {
            whereSql.append(" and extends1 = 'H'");//农村辅助住宅
        } else if (identify == 6) {
            whereSql.append(" and extends1 = 'E'");//农村非住宅
        } else if (identify == 7) {
            whereSql.append(" and extends1 = 'F'");//市政桥梁
        } else if (identify == 8) {
            whereSql.append(" and extends1 = 'G'");//城市自来水
        }
        Query query = this.em.createNativeQuery(sql.append(whereSql).toString());
        List<Object[]> list = query.getResultList();
        return getVOs(list);
    }

    @Override
    public List<YhDisasterEntity> findNoPages(QueryVo queryVo, String userId, int identify) {
        StringBuilder sql = new StringBuilder("select * from yh_disaster");
        StringBuilder whereSql = new StringBuilder(" where is_valid = 1 ");
        if (StringUtils.isNotEmpty(queryVo.getProvince())){
            whereSql.append(" and province =:province");
        }
        if (StringUtils.isNotEmpty(queryVo.getCity())){
            whereSql.append(" and city =:city");
        }
        if (StringUtils.isNotEmpty(queryVo.getArea())){
            whereSql.append(" and area =:area");
        }
        if (StringUtils.isNotEmpty(queryVo.getStartTime())){
            whereSql.append(" and create_time >=:startTime");
        }
        if (StringUtils.isNotEmpty(queryVo.getEndTime())){
            whereSql.append(" and create_time <=:endTime");
        }
        if (identify == 1) {
            whereSql.append(" and extends1 = 'A'");//城镇住宅
        } else if (identify == 2) {
            whereSql.append(" and extends1 = 'B'");//城镇非住宅
        } else if (identify == 3) {
            whereSql.append(" and extends1 = 'D'");//农村独立住宅
        } else if (identify == 4) {
            whereSql.append(" and extends1 = 'C'");//农村集合住宅
        } else if (identify == 5) {
            whereSql.append(" and extends1 = 'H'");//农村辅助住宅
        } else if (identify == 6) {
            whereSql.append(" and extends1 = 'E'");//农村非住宅
        } else if (identify == 7) {
            whereSql.append(" and extends1 = 'F'");//市政桥梁
        } else if (identify == 8) {
            whereSql.append(" and extends1 = 'G'");//城市自来水
        }
        whereSql.append(" and (quality_status!='1' or quality_status is null)");
        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), YhDisasterEntity.class);
        if (StringUtils.isNotEmpty(queryVo.getProvince())){
            query.setParameter("province",queryVo.getProvince());
        }
        if (StringUtils.isNotEmpty(queryVo.getCity())){
            query.setParameter("city",queryVo.getCity());
        }
        if (StringUtils.isNotEmpty(queryVo.getArea())){
            query.setParameter("area",queryVo.getArea());
        }
        if (StringUtils.isNotEmpty(queryVo.getStartTime())){
            query.setParameter("startTime",queryVo.getStartTime());
        }
        if (StringUtils.isNotEmpty(queryVo.getEndTime())){
            query.setParameter("endTime",queryVo.getEndTime());
        }
        List<YhDisasterEntity> list = query.getResultList();
        return list;
    }
}
